package org.example.pack1209;

import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class DemoE {

    // 自动处理异常
    @SneakyThrows
    private static void a() {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "a123456";
        Connection conn = DriverManager.getConnection(url, user, password);
        String sql = "select * from emp where state=1";
        PreparedStatement ps = conn.prepareStatement(sql);
        // 查询需要接收数据库的返回结果
        ResultSet rs = ps.executeQuery();
        // 将结果集展现出来
        while (rs.next()) { // 是否有下一行
            // rs 就是下一行
            int empNo = rs.getInt(1);
            String enname = rs.getString(2);
            String job = rs.getString("job");
            int mgr = rs.getInt(4);
            String hireDate = rs.getString("hireDate");
            double sal = rs.getDouble("sal");
            double comm = rs.getDouble("comm");
            int deptNo = rs.getInt("deptno");
            int state = rs.getInt(9);
            System.out.println(empNo + "," + enname + "," + job + "," + mgr + "," + hireDate + "," + sal + "," + comm + "," + deptNo + "," + state);
        }
        // 关闭
        rs.close();
        ps.close();
        conn.close();
    }

    @SneakyThrows
    private static Object[][] b() {
        Object[][] data = new Object[100][9];
        int i = 0;
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "a123456";
        Connection conn = DriverManager.getConnection(url, user, password);
        String sql = "select * from emp where state=1";
        PreparedStatement ps = conn.prepareStatement(sql);
        // 查询需要接收数据库的返回结果
        ResultSet rs = ps.executeQuery();
        // 将结果集展现出来
        while (rs.next()) { // 是否有下一行
            Object[] row = new Object[9];
            // rs 就是下一行
            int empNo = rs.getInt(1);
            String enname = rs.getString(2);
            String job = rs.getString("job");
            int mgr = rs.getInt(4);
            String hireDate = rs.getString("hireDate");
            double sal = rs.getDouble("sal");
            double comm = rs.getDouble("comm");
            int deptNo = rs.getInt("deptno");
            int state = rs.getInt(9);
            row[0] = empNo;
            row[1] = enname;
            row[2] = job;
            row[3] = mgr;
            row[4] = hireDate;
            row[5] = sal;
            row[6] = comm;
            row[7] = deptNo;
            row[8] = state;
            data[i] = row;
            i++;
        }
        // 关闭
        rs.close();
        ps.close();
        conn.close();
        return data;
    }

    @SneakyThrows
    private static List<Emp> c() {
        List<Emp> list = new ArrayList<>();
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "a123456";
        Connection conn = DriverManager.getConnection(url, user, password);
        String sql = "select * from emp where state=1";
        PreparedStatement ps = conn.prepareStatement(sql);
        // 查询需要接收数据库的返回结果
        ResultSet rs = ps.executeQuery();
        // 将结果集展现出来
        while (rs.next()) { // 是否有下一行
            Emp emp = new Emp();
            // rs 就是下一行
            int empNo = rs.getInt(1);
            String enname = rs.getString(2);
            String job = rs.getString("job");
            int mgr = rs.getInt(4);
            String hireDate = rs.getString("hireDate");
            double sal = rs.getDouble("sal");
            double comm = rs.getDouble("comm");
            int deptNo = rs.getInt("deptno");
            int state = rs.getInt(9);
            // 封装
            emp.setEmpNo(empNo);
            emp.setComm(comm);
            emp.setJob(job);
            emp.setEnname(enname);
            emp.setHireDate(hireDate);
            emp.setDeptNo(deptNo);
            emp.setMgr(mgr);
            emp.setState(state);
            emp.setSal(sal);
            // 将对象添加到集合中
            list.add(emp);
        }
        // 关闭
        rs.close();
        ps.close();
        conn.close();
        return list;
    }

    // 按照主键id查询
    @SneakyThrows
    private static Emp d(int id) {
        Emp emp = new Emp();
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "a123456";
        Connection conn = DriverManager.getConnection(url, user, password);
        String sql = "select * from emp where state=1 and empno=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            int empNo = rs.getInt(1);
            String enname = rs.getString(2);
            String job = rs.getString("job");
            int mgr = rs.getInt(4);
            String hireDate = rs.getString("hireDate");
            double sal = rs.getDouble("sal");
            double comm = rs.getDouble("comm");
            int deptNo = rs.getInt("deptno");
            int state = rs.getInt(9);
            // 封装
            emp.setEmpNo(empNo);
            emp.setComm(comm);
            emp.setJob(job);
            emp.setEnname(enname);
            emp.setHireDate(hireDate);
            emp.setDeptNo(deptNo);
            emp.setMgr(mgr);
            emp.setState(state);
            emp.setSal(sal);
            return emp;
        }
        return null;
    }

    @SneakyThrows
    private static List<Emp> e(String name) {
        List<Emp> list = new ArrayList<>();
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "a123456";
        Connection conn = DriverManager.getConnection(url, user, password);
        String sql = "select * from emp where state=1 and ename like ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, "%" + name + "%");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) { // 是否有下一行
            Emp emp = new Emp();
            // rs 就是下一行
            int empNo = rs.getInt(1);
            String enname = rs.getString(2);
            String job = rs.getString("job");
            int mgr = rs.getInt(4);
            String hireDate = rs.getString("hireDate");
            double sal = rs.getDouble("sal");
            double comm = rs.getDouble("comm");
            int deptNo = rs.getInt("deptno");
            int state = rs.getInt(9);
            // 封装
            emp.setEmpNo(empNo);
            emp.setComm(comm);
            emp.setJob(job);
            emp.setEnname(enname);
            emp.setHireDate(hireDate);
            emp.setDeptNo(deptNo);
            emp.setMgr(mgr);
            emp.setState(state);
            emp.setSal(sal);
            // 将对象添加到集合中
            list.add(emp);
        }
        return list;
    }

    @SneakyThrows
    private static List<Emp> f(int page, int size) {
        List<Emp> list = new ArrayList<>();
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "a123456";
        Connection conn = DriverManager.getConnection(url, user, password);
        String sql = "select * from emp where state=1 limit ?,?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, (page - 1) * size);
        ps.setInt(2, size);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) { // 是否有下一行
            Emp emp = new Emp();
            // rs 就是下一行
            int empNo = rs.getInt(1);
            String enname = rs.getString(2);
            String job = rs.getString("job");
            int mgr = rs.getInt(4);
            String hireDate = rs.getString("hireDate");
            double sal = rs.getDouble("sal");
            double comm = rs.getDouble("comm");
            int deptNo = rs.getInt("deptno");
            int state = rs.getInt(9);
            // 封装
            emp.setEmpNo(empNo);
            emp.setComm(comm);
            emp.setJob(job);
            emp.setEnname(enname);
            emp.setHireDate(hireDate);
            emp.setDeptNo(deptNo);
            emp.setMgr(mgr);
            emp.setState(state);
            emp.setSal(sal);
            // 将对象添加到集合中
            list.add(emp);
        }
        return list;
    }

    public static void main(String[] args) {
        a();
        // b
        /*Object[][] data = b();
        for (int i = 0; i < data.length; i++) {
            for (int j = 0; j < data[i].length; j++) {
                System.out.print(data[i][j] + ",");
            }
            System.out.println();
        }*/
        // c
        /*List<Emp> list = c();
        list.forEach(System.out::println);*/
        // d
        /*Emp emp = d(7969);
        System.out.println(emp);*/
        // e
        /*List<Emp> list = e("张");
        list.forEach(System.out::println);*/
        // f
        //List<Emp> list = f(2,10);
        //list.forEach(System.out::println);
    }
}
/*
使用JDBC实现图书借阅系统
1. 每位用户必须登陆后才能借阅
2. 如果未注册，提供注册功能，用户可以通过问题找回密码
3. 登陆后用户可以借阅、归还、查询
4. 用户登陆后应提醒未还图书
5. 归还时计算费用
自己创建数据库和对应的表实现功能
 */